﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using PMS.ENT;
using PMS.Utilities;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;

namespace PMS.DAL
{
    public class NotificationDAL
    {
        #region Common
        private static NotificationENT CreateNotificationFromReader(IDataReader reader)
        {
            NotificationENT item = new NotificationENT();
            try
            {

                if (!reader.IsDBNull(reader.GetOrdinal(NotificationColumns.NotificationID.ToString()))) item.NotificationID = Utility.IntParse(reader[NotificationColumns.NotificationID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(NotificationColumns.UserID.ToString()))) item.UserID = Utility.IntParse(reader[NotificationColumns.UserID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(NotificationColumns.GroupID.ToString()))) item.GroupID = Utility.IntParse(reader[NotificationColumns.GroupID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(NotificationColumns.CreatedBy.ToString()))) item.CreatedBy = Utility.IntParse(reader[NotificationColumns.CreatedBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(NotificationColumns.CreatedDate.ToString()))) item.CreatedDate = Utility.DateTimeParse(reader[NotificationColumns.CreatedDate.ToString()]);

                if (!reader.IsDBNull(reader.GetOrdinal(NotificationColumns.Description.ToString()))) item.Description = Utility.StringParse(reader[NotificationColumns.Description.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(NotificationColumns.Link.ToString()))) item.Link = Utility.StringParse(reader[NotificationColumns.Link.ToString()]);

                if (!reader.IsDBNull(reader.GetOrdinal(NotificationColumns.IsRead.ToString()))) item.IsRead = Utility.IntParse(reader[NotificationColumns.IsRead.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(NotificationColumns.ReadDate.ToString()))) item.ReadDate = Utility.DateTimeParse(reader[NotificationColumns.ReadDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(NotificationColumns.IsDelete.ToString()))) item.IsDelete = Utility.IntParse(reader[NotificationColumns.IsDelete.ToString()]);



            }
            catch
            {
                throw;
            }
            return item;
        }
        #endregion

        #region Get
        public static NotificationENT GetNotificationByID(int NotificationID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procNotificationGetByID.ToString());
                database.AddInParameter(dbCommand, "@NotificationID", DbType.Int64, NotificationID);

                NotificationENT Notification = new NotificationENT();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        Notification = CreateNotificationFromReader(reader);
                    }
                    reader.Close();
                }

                return Notification;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetNotificationByID");
            }
        }


        public static NotificationCollection GetNotificationListByUserID(int userId, string orderBy, string orderDirection, int page, int pageSize, out int totalRecords)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procNotificationGetListByUserID.ToString());
                NotificationCollection listNotification = new NotificationCollection();

                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userId);
                database.AddInParameter(dbCommand, "@OrderBy", DbType.String, orderBy);
                database.AddInParameter(dbCommand, "@OrderDirection", DbType.AnsiString, orderDirection);
                database.AddInParameter(dbCommand, "@Page", DbType.Int32, page);
                database.AddInParameter(dbCommand, "@PageSize", DbType.Int32, pageSize);
                database.AddOutParameter(dbCommand, "@TotalRecords", DbType.Int32, 0);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listNotification.Add(CreateNotificationFromReader(reader));
                    }
                    reader.Close();
                }

                totalRecords = (int)database.GetParameterValue(dbCommand, "@TotalRecords");

                return listNotification;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetNotificationList");
            }
        }


        public static int GetNotificationUnreadNumber(int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procNotificationGetUnreadNumber.ToString());
                NotificationCollection listNotification = new NotificationCollection();

                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                database.AddOutParameter(dbCommand, "@TotalRecords", DbType.Int32, 0);
                IDataReader reader = database.ExecuteReader(dbCommand);
                int totalRecords = (int)database.GetParameterValue(dbCommand, "@TotalRecords");

                return totalRecords;
            }
            catch (Exception)
            {

                throw new DataAccessException("GetNotificationUnreadNumber");
            }
        }
        #endregion

        #region Insert, Update, Delete

        public static int InsertNotification(NotificationENT Notification)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procNotificationInsert.ToString());
                int NotificationID = 0;
                database.AddOutParameter(dbCommand, "@NotificationID", DbType.Int32, Notification.NotificationID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, Notification.UserID);
                database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, Notification.GroupID);
                database.AddInParameter(dbCommand, "@CreatedBy", DbType.Int32, Notification.CreatedBy);
                database.AddInParameter(dbCommand, "@Description", DbType.String, Notification.Description);
                database.AddInParameter(dbCommand, "@Link", DbType.String, Notification.Link);
                database.AddInParameter(dbCommand, "@IsRead", DbType.Int32, Notification.IsRead);
                database.AddInParameter(dbCommand, "@IsDelete", DbType.Int32, Notification.IsDelete);
                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    NotificationID = Utility.IntParse(database.GetParameterValue(dbCommand, "@NotificationID"));
                    return NotificationID;
                }
                return -1;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertNotification");
            }
        }

        public static int InsertNotification(NotificationENT Notification, DbTransaction trans)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procNotificationInsert.ToString());
                int NotificationID = 0;
                database.AddOutParameter(dbCommand, "@NotificationID", DbType.Int32, Notification.NotificationID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, Notification.UserID);
                database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, Notification.GroupID);
                database.AddInParameter(dbCommand, "@CreatedBy", DbType.Int32, Notification.CreatedBy);
                database.AddInParameter(dbCommand, "@Description", DbType.String, Notification.Description);
                database.AddInParameter(dbCommand, "@Link", DbType.String, Notification.Link);
                database.AddInParameter(dbCommand, "@IsRead", DbType.Int32, Notification.IsRead);
                database.AddInParameter(dbCommand, "@IsDelete", DbType.Int32, Notification.IsDelete);
                database.ExecuteNonQuery(dbCommand, trans);
                NotificationID = Utility.IntParse(database.GetParameterValue(dbCommand, "@NotificationID"));
                return NotificationID;

            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertNotification");
            }
        }

        public static bool InsertNotification(NotificationCollection listNotification)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                using (DbConnection conn = database.CreateConnection())
                {
                    conn.Open();
                    DbTransaction trans = conn.BeginTransaction();

                    try
                    {

                        foreach (NotificationENT item in listNotification)
                        {
                            int notificationID = InsertNotification(item, trans);
                            if (notificationID <= 0)
                            {
                                throw new DataAccessException("BLM_ERR_InsertNotification");
                            }
                        }
                        trans.Commit();
                        return true;

                    }
                    catch (SqlException ex)
                    {
                        trans.Rollback();
                        LogDAL.LogEx("BLM_ERR_InsertNotification", ex);
                        throw new DataAccessException("BLM_ERR_InsertNotification");
                    }
                    catch (DataAccessException)
                    {
                        trans.Rollback();
                        throw;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            catch (ConfigurationException ex)
            {
                LogDAL.LogEx("BLM_ERR_Configuration", ex);
                throw new DataAccessException("BLM_ERR_Configuration");
            }
            catch (SqlException)
            {
                throw;
            }
            catch (DataAccessException)
            {
                throw;
            }
            catch (Exception ex)
            {
                LogDAL.LogEx("BLM_ERR_InsertNotification", ex);
                throw new DataAccessException("BLM_ERR_InsertNotification");
            }
        }



        public static int UpdateNotificationIsRead(int notificationID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procNotificationUpdateIsRead.ToString());

                database.AddInParameter(dbCommand, "@NotificationID", DbType.Int32, notificationID);


                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    int NotificationID = Utility.IntParse(database.GetParameterValue(dbCommand, "@NotificationID"));
                    return NotificationID;
                }
                return -1;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_UpdateNotificationIsRead");
            }
        }

        public static int DeleteNotification(string strNotificationID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procNotificationDelete.ToString());
                database.AddInParameter(dbCommand, "@StrNotificationID", DbType.String, strNotificationID);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_DeleteNotification");
            }
        }
        #endregion
    }
}
